<?php
/**
 * Mysql AbstractSynchronizer implementation
 * @author Artur Kmera <artur.kmera@3code.pl>
 * @version 0.9
 * @package ORM
 * @subpackage Schema
 * @link http://arrowplatform.org/orm
 * @copyright 2011 Arrowplatform 
 * @license GNU LGPL
 * @todo comment rest
*/
class MysqlSynchronizer extends AbstractSynchronizer{
		
	/**
	 * (non-PHPdoc)
	 * @see AbstractSynchronizer::getSchemaMismatches()
	 */		
	public function getSchemaMismatches(  Datasource $ds, Schema $schema ){
		$mismatches = array();
		
		$conn = $ds->getConnection();
		$statement = $conn->prepare("show tables");
		$statement->execute();
		$dbTables = array();
		while( $row = $statement->fetch( PDO::FETCH_NUM ) ) {
			$dbTables[] = $row[0];
		}
		$schemaTables = array();
		foreach($schema->tables as $table){
			if(!in_array($table->table, $dbTables)){
				//$this->createTable($table,$conn);
				$mismatches[] = new SchemaMismatch($schema, $ds, $table, $table, SchemaMismatch::NOT_EXISTS);
			}else{
				$tmp = $this->checkTableFields($schema, $ds, $table);
				$mismatches = array_merge($mismatches, $tmp);
			}
			//$schemaTables[] = $table->table;
		}
		
		//iterating over ds tables ( only check if exists in schema )
		foreach( $dbTables as $table ){
			$exists = false;
			foreach($schema->tables as $schemaTable){
				if($schemaTable->table == $table){
					$exists = true;
					break;
				}
			}	
			if(!$exists)
				$mismatches[] = new  DatasourceMismatch( $schema, $ds, "Database", $table, DatasourceMismatch::ELEMENT_TYPE_TABLE, DatasourceMismatch::NOT_EXISTS);
		}
		
		return $mismatches;
	}
	
	/**
	 * (non-PHPdoc)
	 * @see AbstractSynchronizer::schemaToDatabase()
	 */
	public function schemaToDatasource( Datasource $ds, Schema $schema){
		
		$conn = $ds->getConnection();
		$mismatches = $this->getSchemaMismatches($ds, $schema);
		
		$resolved = array();
		foreach($mismatches as $mismatch){
			//schema mismatch
			$resolved[] = $this->resolveMismatch( $mismatch);
		}
		
		return $resolved;
	}
	
	/**
	 * (non-PHPdoc)
	 * @see AbstractSynchronizer::resolveMismatch()
	 */
	public function resolveMismatch( AbstractMismatch $mismatch){
		$ds = $mismatch->datasource;
		$conn = $ds->getConnection();
		$resolvedMismatch = new ResolvedMismatch();
		$resolvedMismatch->mismatch = $mismatch;
		
		$sql = "";
		
		
		if($mismatch instanceof SchemaMismatch){
			//table
			if( $mismatch->element instanceof Table)
				$sql = $this->createTable( $mismatch->element, $conn);
			
			//field
			if( $mismatch->element instanceof Field){
				if($mismatch->type == SchemaMismatch::NOT_EXISTS )
					$sql = $this->createField( $mismatch->parentElement, $mismatch->element, $conn );
				if( $mismatch->type == SchemaMismatch::NOT_EQUALS || $mismatch->type == SchemaMismatch::INDEX_NOT_EQUALS )
					$sql = $this->updateField( $mismatch->parentElement, $mismatch->element, $conn );
				if( $mismatch->type == SchemaMismatch::NAME_NOT_EQUALS )
					$sql = $this->updateField( $mismatch->parentElement, $mismatch->element, $conn, true );
			}
				
		}
		
		if($mismatch instanceof DatasourceMismatch){
			if($this->getMode() == self::MODE_STRICT){
				if($mismatch->elementType == DatasourceMismatch::ELEMENT_TYPE_TABLE ){
					$sql = $this->deleteTable( $mismatch->element ,$conn );
				}
				if($mismatch->elementType == DatasourceMismatch::ELEMENT_TYPE_FIELD ){
					$sql = $this->deleteField(  $mismatch->parentElement, $mismatch->element , $conn);
				}
			}
		}
		$resolvedMismatch->additionalData = $sql; 
		$resolvedMismatch->timestamp = time();
		
		if( true ){
			$resolvedMismatch->success = true;
			if($mismatch->schema->autoIncrementBuildVersion)
				$mismatch->schema->incrementBuildVersion();
		}
			
		
			
		$resolvedMismatch->additionalData = $sql;
		
		return $resolvedMismatch;	
		
	}
	
	/**
	 * (non-PHPdoc)
	 * @see AbstractSynchronizer::databaseToSchema()
	 * @todo Insert resovedmatch object and return it 
	 */
	public function datasourceToSchema(Datasource $ds, Schema $schema){
		$conn = $ds->getConnection();
		$mismatches = $this->getSchemaMismatches($ds, $schema);
		
		foreach($mismatches as $mismatch){
			if( $mismatch->elementType == DatasourceMismatch::ELEMENT_TYPE_TABLE ){
				$table = new Table();
				$table->classPath = "auto_path.Auto_".$mismatch->element;
				$table->baseClassPath = "auto.Persistent";
				$table->table = $mismatch->element;
				$schema->tables[] = $table;
			}
		}
		
		$mismatches = $this->getSchemaMismatches($ds, $schema);
		
		foreach($mismatches as $mismatch){
			
			if( $mismatch->elementType == DatasourceMismatch::ELEMENT_TYPE_FIELD ){
				
				$field = new Field();
				$field->name = $mismatch->element;
				
				$statement = $conn->prepare("SHOW COLUMNS FROM {$mismatch->parentElement} where Field='{$mismatch->element}'");
				$statement->execute();
				$column = $statement->fetch();
				
				$field->pKey = $column["Key"] == "PRI"?true:false;
				$field->autoincrement = $column["Extra"] == "auto_increment"?true:false;
				$field->default = $column["Default"];
				$field->required = $column["Null"]=="NO"?true:false;
				
				$tmp = explode("(",$column["Type"]);
				$type = $tmp[0];
						
				if( in_array($column["Type"], array("text", "mediumtext", "longtext")) ){
					
					if($type == "text") $size = 65535;
					elseif($type == "mediumtext") $size = 16777215;
					elseif($type == "longtext") $size = 4294967295;
					$type = "LONGVARCHAR"; 
				}else{
					$size = str_replace(")", "", $tmp[1]);
				}
				$field->type = $type;
				$field->size = $size;
				$table = $schema->getTableByTable( $mismatch->parentElement );
				$table->fields[] = $field;
			}
		}
	}
	
	/**
	 * Creating table in database
	 * @param Table $table
	 * @param PDO $conn
	 */
	private function createTable(Table $table, PDO $conn){
		$sql = "CREATE TABLE {$table->table}(";
		$count = count($table->fields);
		foreach($table->fields as $index => $field){
			$sql.= $this->getFieldCreationCode($table, $field);
			if($index+1<$count) $sql.=",\n";
		}
		$sql.= ")";
	
		$statement = $conn->prepare($sql);
		$statement->execute();
		return $sql;
	}
	
	private function checkTableFields(Schema $schema, Datasource $ds,  Table $table){
		$conn = $ds->getConnection();
		$mismatches = array();
		
		$statement = $conn->prepare("SHOW COLUMNS FROM `{$table->table}`");
		$statement->execute();
		$columns = $result = $statement->fetchAll(PDO::FETCH_ASSOC);

		//iterating over schema fields ( only check if exists, rest of checks  are in the next loop )
		foreach( $table->fields as $field ){
			$exists = false;
		
			foreach($columns as $column){
				if($field->name == $column["Field"] || $field->oldName == $column["Field"]){
					$exists = true;
					break;
				}
			}	
			if(!$exists)
				$mismatches[] = new SchemaMismatch($schema, $ds, $table, $field, SchemaMismatch::NOT_EXISTS);
		}
		
		$i = 0;
		//iterating over db columns
		foreach($columns as $column){
					
			if( !isset($table->fields[$i]) || $table->fields[$i]->name != $column["Field"]){
				$exists = false;
				if( isset($table->fields[$i])){
					//checks that field exists in db
					foreach( $columns as $tmp ){ 
						if( $table->fields[$i] == $tmp["Field"] ){
							$exists = true;
							break;
						}
					}
				}
				
				
				if($exists){
					$mismatches[] = new SchemaMismatch($table, $table->fields[$i], SchemaMismatch::NOT_EQUALS);
				}else{
					//checks that field exists in schema
					$exists = false;
					$existsField = false;
					foreach( $table->fields as $field ){ 
						if( $field->name == $column["Field"] ){
							$exists = true;
							$existsField = $field;
							break;
						}
					}
					
					$existByOldName = false;
					if(!$exists){
						$existsField = false;
						foreach( $table->fields as $field ){ 
							if( $field->oldName == $column["Field"] ){
								$existByOldName = true;
								$existsField = $field;
								break;
							}
						}
					}
					
					if( $existByOldName)
						$mismatches[] = new SchemaMismatch($schema, $ds, $table, $existsField, SchemaMismatch::NAME_NOT_EQUALS);
					elseif($exists)
						$mismatches[] = new SchemaMismatch($schema, $ds,$table, $existsField, SchemaMismatch::INDEX_NOT_EQUALS);
					else{
						$mismatches[] = new DatasourceMismatch($schema, $ds,$table->table, $column["Field"], DatasourceMismatch::ELEMENT_TYPE_FIELD,  SchemaMismatch::NOT_EXISTS);
						$i--;
					}
				}
				
			}else{
				if( $this->checkField( $table->fields[$i], $column ) == false)
					$mismatches[] = new SchemaMismatch($schema, $ds, $table, $table->fields[$i], SchemaMismatch::NOT_EQUALS);
			}
				
			$i++;
		}	
		
		return $mismatches;
		
	}
	
	private function checkField(Field $field, $column){
		
		
		if( $field->pKey && $column["Key"] != "PRI")
			return false;
		if( $field->autoincrement && $column["Extra"] != "auto_increment")
			return false;
		if( $field->default && $field->default != $column["Default"])
			return false;
		
		if( $field->required && $column["Null"]!="NO")
			return false;
		
		$tmp = explode("(",$column["Type"]);
		$type = $tmp[0];
		
		$size = false;
		
		//longvarchar is changed for text and it not posses size
		if( $field->size && $field->type != "LONGVARCHAR"){
			$size = str_replace(")", "", $tmp[1]);
			if( $field->size != $size)
				return false;
		}
		
		
		
		$types = array( "INTEGER" => "INT", "LONGVARCHAR" => "TEXT" );
		$testType = strtolower( str_replace(array_keys($types), $types, $field->type));
		if($testType == "text" && $size < 65535 )
			$testType = "text";
		elseif($testType == "text" && $size < 16777215 )
			$testType = "mediumtext";
		elseif($testType == "text" && $size > 16777215 )
			$testType = "longtext";
			
		
		if( $testType != $type )
			return  false;
		
		return true;
	}
	
	private function checkIndexes(Schema $schema, Datasource $ds,  Table $table){
		$conn = $ds->getConnection();
		$mismatches = array();
		
		$statement = $conn->prepare("SHOW INDEXES  FROM `{$table->table}`");
		$statement->execute();
		$indexes = $result = $statement->fetchAll(PDO::FETCH_ASSOC);
		
	}
	private function checkForeignKeys(Schema $schema, Datasource $ds,  Table $table){
		$conn = $ds->getConnection();
		$mismatches = array();
		
		$statement = $conn->prepare("SHOW CREATE TABLE `{$table->table}`");
		$statement->execute();
		$result = $statement->fetch(PDO::FETCH_NUM);
		$createStatement = $result[1];
		
		/*
		 select * from information_schema.table_constraints
    		-> where table_schema = schema()
    		-> and table_name = 'my_test_table';
		 */
		
		/*
		 select * from information_schema.key_column_usage
    		where  table_schema = schema()
    		and table_name = 'my_test_table'
    		and constraint_name = 'type_id';
		 */
		  
	}
	
	
	private function deleteTable( $table, $conn){
		$sql = "DROP TABLE  `{$table}`";
		$conn->exec($sql);
		return $sql;
	}
	
	private function createField(Table $table, Field $field, $conn){
		$index = array_search($field, $table->fields);
		if($index == 0)
			$prev = "FIRST";
		else
			$prev = "AFTER `{$table->fields[$index-1]->name}`";
		
		$sql = $this->getFieldCreationCode($table, $field);
		$sql = "ALTER TABLE  `{$table->table}` ADD  $sql  {$prev}";
		$conn->exec($sql);
		
		return $sql;
	}
	
	private function updateField(Table $table, Field $field, $conn, $oldName = false){
		$index = array_search($field, $table->fields);
		if($index == 0)
			$prev = "FIRST";
		else
			$prev = "AFTER `{$table->fields[$index-1]->name}`";
		
		$sql = $this->getFieldCreationCode($table, $field);
		$name = $oldName?$field->oldName:$field->name;
		
		$sql = "ALTER TABLE  `{$table->table}` CHANGE `{$name}`  $sql  {$prev}";
		$conn->exec($sql);
		return $sql;
	}
	
	private function deleteField( $table, $field, $conn){
		$sql = "ALTER TABLE `{$table}` DROP `{$field}`";
		$conn->exec($sql);
		return $sql;
	}
	
	

	private function translateType($type){
		$types = array( "INTEGER" => "INT", "LONGVARCHAR" => "TEXT" );
		return str_replace(array_keys($types), $types, $type);
	}
	
	
	private function getFieldCreationCode( Table $table, Field $field ){
		$sql="`{$field->name}` {$this->translateType($field->type)}";
			
		if($field->size)
			$sql.="({$field->size})";

		if($field->autoincrement)
			$sql.=" AUTO_INCREMENT";
		
		if($field->pKey)
			$sql.=" PRIMARY KEY";

		if($field->required)
			$sql.=" NOT NULL";
			
		if($field->default)
			$sql.=" DEFAULT  '{$field->default}'";
		
		return $sql;
	}
	
}

?>